/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package evaluate.autocomplete;

import system.general.Exc;
import system.general.Label;
import system.general.Logged;
import system.general.Persistencia;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.convert.Converter;
import javax.faces.convert.FacesConverter;

@ManagedBean(name = "evaluate_autocompleteFind")
@FacesConverter(value = "evaluate_autocompleteFind")
@RequestScoped
public class Find implements Converter {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private Beans selectedBean = new Beans();
    private Label label = new Label();
    private String sql = "";
    private String command = "SELECT DISTINCT "
            + "sg.id,sg.carrera,sg.observacion,sg.idl_accion, "
            + "pg.nombre || ' ' || pg.apellido AS persona, uf.nombre as facultad, "
            + "ug.id as id_universidad, ug.nombre as universidad "
            + "FROM public.v_solicitud_general sg "
            + "LEFT JOIN public.v_solicitud_sede ss "
            + "ON (sg.id=ss.id_solicitud) "
            + "LEFT JOIN public.v_universidad_facultad uf "
            + "ON (sg.id_facultad=uf.id) "
            + "LEFT JOIN public.v_universidad_sede us "
            + "ON (ss.id_sede =us.id) "
            + "LEFT JOIN public.v_universidad_general  ug "
            + "ON (uf.id_universidad=ug.id) "
            + "LEFT JOIN public.v_persona_general pg "
            + "ON (sg.id_recepcionista=pg.id) "
            + "WHERE ";
    private String order = "ORDER BY sg.carrera";

    public Find() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
    }

    public Beans getSelected() {
        return selectedBean;
    }

    public void setSelected(Beans bean) {
        if (bean != null) {
            logged.setMasterId(selectedBean.getRequestBeans().getId());
        }
    }

    public List<Beans> autoComplete(String data) throws Exc, SQLException, Exception {
        //data = data.replace(" ", "|");
        Map<String, String> map = new LinkedHashMap<>();
        map.clear();

        try {
            if (Integer.parseInt(data) > 0) {
                map.put("sg.id  = " + data, " OR ");
            }
        } catch (NumberFormatException ex) {
        }
        map.put("lower(pg.nombre) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(ug.nombre) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(uf.nombre) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(us.nombre) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(sg.carrera) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(sg.observacion) SIMILAR TO '%(" + strChar(data) + ")%'", "  ");

        // map.put("lower(cs.observacion) SIMILAR TO '%(" + strChar(data) + ")%'", " ");
        return byMap(map);
    }

    public List<Beans> byMap(Map map) throws Exc, SQLException, Exception {
        Iterator it = map.entrySet().iterator();
        StringBuilder criteria = new StringBuilder("");
        while (it.hasNext()) {
            Map.Entry e = (Map.Entry) it.next();
            criteria.append(e.getKey().toString());
            criteria.append(e.getValue().toString());
        }
        sql = command + criteria + order;
        return executeSql(sql);
    }

    private List<Beans> executeSql(String sql) throws Exc, SQLException, Exception {
        List<Beans> list = new ArrayList<>();
        list.clear();
        ResultSet rsts = persistencia.executeRsts(sql);
        while (rsts.next()) {
            Beans beans = new Beans(
                    rsts.getInt("id"),
                    rsts.getString("carrera"),
                    rsts.getString("universidad"),
                    label.name("request.general", "accion" + rsts.getInt("idl_accion")),
                    rsts.getString("facultad"),
                    rsts.getString("persona"));
            list.add(beans);
        }
        //Desconecta de la base de datos debido a que es un resultset y podria quedar la conexion activa
        persistencia.dbDisconnect();
        return list;
    }

    private String strChar(String data) {
        data = data.toLowerCase().trim();
        data = data.replaceAll("á", "a");
        data = data.replaceAll("é", "e");
        data = data.replaceAll("í", "i");
        data = data.replaceAll("ó", "o");
        data = data.replaceAll("ú", "u");
        data = data.replaceAll("a", "[a,á]");
        data = data.replaceAll("e", "[e,é]");
        data = data.replaceAll("i", "[i,í]");
        data = data.replaceAll("o", "[o,ó]");
        data = data.replaceAll("u", "[u,ú]");
        return data;
    }

    @Override
    public Object getAsObject(FacesContext facesContext, UIComponent component, String submittedValue) {
        if (submittedValue.trim().equals("0")) {
            return null;
        }
        int id = Integer.parseInt(submittedValue.substring(0, submittedValue.indexOf("-")));
        String carrera = submittedValue.substring(submittedValue.indexOf("-") + 1, submittedValue.length());
        logged.setMasterId(id);
        logged.setMasterName(carrera);
        return null;
    }

    @Override
    public String getAsString(FacesContext facesContext, UIComponent component, Object value) {
        if (value == null || value.equals("")) {
            return "";
        }

        return String.valueOf(((Beans) value).getRequestBeans().getId()) + "-"
                + "" + ((Beans) value).getRequestBeans().getCarrera() + ""
                + " " + ((Beans) value).getSedeBeans().getNombre();
    }
}
